#coding:utf-8

"""
mysql操作类
"""

import MySQLdb

#引入配置参数
from config import global_config

class mysqlAction:

	"""
	docstring for ClassName
	"""
	
	def __init__(self,database):

		self.connect(database)

	def connect(self,database):

		host = global_config.getRaw('mysqlConfig', 'hostname')
		user = global_config.getRaw('mysqlConfig', 'username')
		passwd = global_config.getRaw('mysqlConfig', 'password')
		db = global_config.getRaw('mysqlConfig', 'database')
		#db = database
		port = global_config.getRaw('mysqlConfig', 'port')
		#连接
		self.conn = MySQLdb.connect(host=host,user=user,passwd=passwd,db=db,port=int(port),charset="utf8")
		self.cursor = self.conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)

	def close(self):

		self.cursor.close()
		self.conn.close()
		return

	"""
	execute custom sql query

	sql = 'select * from users limit 0, 5'
	rows = dbconn.query(sql)
	for row in rows:
		print(row)

	"""
	def query(self, sql, fetchone=False):

		self.cursor.execute(sql)
		self.conn.commit()

		return self.cursor.fetchone() if fetchone else self.cursor.fetchall()
	
	"""
	mysql insert() function

	param = {'email': '1232131@qq.com', 'password': '123456'}
	dbconn.insert(table='users', data=param)

	"""
	def insert(self, table, data):
		params = self.join_field_value(data)

		sql = "INSERT IGNORE INTO {table} SET {params}".format(
			table=table, params=params)
		self.cursor.execute(sql, tuple(data.values()))
		last_id = self.conn.insert_id()

		self.conn.commit()

		return last_id

	"""
	mysql delete() function
	sql.PreparedStatement method

	condition = {'email': '1232131@qq.com'}
	rows = dbconn.delete(table='users', condition, limit='1')
	print('deleted {} records success..'.format(rows))

	"""
	def delete(self, table, condition=None, limit=None):

		prepared = []

		if not condition:
			where = '1'
		elif isinstance(condition, dict):
			where = self.join_field_value(condition, ' AND ')
			prepared.extend(condition.values())
		else:
			where = condition

		limits = "LIMIT {limit}".format(limit=limit) if limit else ""

		sql = "DELETE FROM {table} WHERE {where} {limits}".format(
			table=table, where=where, limits=limits)

		if not prepared:
			result = self.cursor.execute(sql)
		else:
			result = self.cursor.execute(sql, tuple(prepared))

		self.conn.commit()
		return result
	
	
	"""
	mysql update() function 
	Use sql.PreparedStatement method

	user = {'email': '1232131@qq.com', 'password': '123456'}
	cond = {'email': '1232131@qq.com'}
	rows = dbconn.update(table='users', data=user, condition=cond)
	print('update {} records success..'.format(rows))
	"""	
	def update(self, table, data, condition=None):

		prepared = []
		params = self.join_field_value(data)
		prepared.extend(data.values())

		if not condition:
			where = '1'
		elif isinstance(condition, dict):
			where = self.join_field_value(condition, ' AND ')
			prepared.extend(condition.values())
		else:
			where = condition

		sql = "UPDATE IGNORE {table} SET {params} WHERE {where}".format(
			table=table, params=params, where=where)

		# check PreparedStatement
		if not prepared:
			result = self.cursor.execute(sql)
		else:
			result = self.cursor.execute(sql, tuple(prepared))

		self.conn.commit()
		
		return result
	
	def join_field_value(self, data, glue=', '):
		sql = comma = ''
		if data:
			for key in data.keys():
				sql += "{}`{}` = %s".format(comma, key)
				comma = glue
		return sql